/*
 * LegacyDB.java
 * 
 * Copyright 2008 David D. Emory
 * 
 * This file is part of Five Points. See <http://www.fpdev.org> for
 * additional information regarding the project.
 * 
 * Five Points is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * Five Points is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with Five Points.  If not, see <http://www.gnu.org/licenses/>.
 */
package org.fpdev.core.data;

import java.awt.geom.Rectangle2D;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;
import org.fpdev.core.FPEngine;
import org.fpdev.core.DataPackage;
import org.fpdev.core.basenet.*;

/**
 * The base class for the database used to store the base network. Most common
 * SQL query functionality for writing to / reading from the database is
 * specified here. 
 * 
 * This class is abstract; subclasses provide implementations specific to
 * individual RDBMS platforms (MySQL, Derby, etc.)
 * 
 * @author demory
 */
public abstract class LegacyDB {

  protected FPEngine engine_;
  protected DataPackage dataPkg_;
  
  protected boolean initialized_;
  protected String user_,  pw_;
  protected Connection conn_;
  protected Statement s_;
  private String activeTable_; // name of the current table
  // Text-based insert operations can be consolidated into single SQL queries
  // (w/ comma-separated insertion items) to speed up large batch insert tasks.
  private String insOps_; // the current comma-separated list of insertions
  private int maxInsOps_,  insOpsCount_; // the maximum/current number of insertions for the single statement

  // similar system for BLOB-based insert operations:
  private String blobInsOps_,  activeBlobTable_;
  private int maxBlobInsOps_,  blobInsOpsCount_;
  private List<ByteArray> blobs_;
  // TODO: replace with enum
  public static final int DB_DERBY = 1;
  public static final int DB_MYSQL = 2;

  // CONSTRUCTORS
  public LegacyDB(FPEngine engine, DataPackage pkg, String user, String pw, ZipFile zipFile) {

    initialized_ = false;
    engine_ = engine;
    dataPkg_ = pkg;
    user_ = user;
    pw_ = pw;
    conn_ = null;
    s_ = null;

    insOps_ = activeTable_ = "";
    maxInsOps_ = 256;
    insOpsCount_ = 0;

    blobInsOps_ = activeBlobTable_ = "";
    maxBlobInsOps_ = 256;
    blobInsOpsCount_ = 0;
    blobs_ = new ArrayList<ByteArray>();

    if (zipFile != null) {
      readFromZip(zipFile);
    }
    connect();
  }

  // ACCESSORS
  public static int getRDBMSCode(String rdbmsStr) {
    if (rdbmsStr.compareTo("derby") == 0) {
      return DB_DERBY;
    } else if (rdbmsStr.compareTo("mysql") == 0) {
      return DB_MYSQL;
    }
    return 0;
  }

  public boolean initialized() { return initialized_; }

  // INIT/DISCONNECT METHODS
  public void connect() {
    try {

      Class.forName(jdbcClassname()).newInstance();
      System.out.println("Loaded the appropriate driver.");

      init(); // initialize the connection
      if(!initialized()) {
        System.out.println("Database initialization failed");
        return;
      }
      
      // temp: count links as test:
      //System.out.println("Link count: " + this.countRows("links"));
      
      // initialize the prepared statements for later use:
      prepareStatements();

    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }
  
  // PREPARED STATEMENTS -- used for frequently-repeated tasks
  private PreparedStatement updateLinkPS_;
  private PreparedStatement updateNodePS_;
  private PreparedStatement selLinkRangePS_;
  private PreparedStatement updateAddrRangePS_;
  private PreparedStatement queryShpPtsPS_;
  private PreparedStatement updateShpPtsPS_;
  private PreparedStatement insertShpPtsPS_;
  private PreparedStatement insertElevPS_;
  private PreparedStatement insertScenLinkTypePS_;
  private PreparedStatement deleteScenLinkTypesPS_;
  
  /*private PreparedStatement insertLocationLogPS_;
  private PreparedStatement insertTripLogPS_;
  
  private PreparedStatement queryTripLinksPS_;
  private PreparedStatement insertTripLinksPS_;*/


  public void prepareStatements() {
    try {
      updateLinkPS_ = conn_.prepareStatement("UPDATE links SET type = ?,scenario = ?,fnode = ?, tnode = ?, name = ?, len = ?, onrtenet = ?, bbx1 = ?, bby1 = ?, bbx2 = ?, bby2 = ? WHERE id = ?");
      updateNodePS_ = conn_.prepareStatement("UPDATE nodes SET scenario = ?,x = ?, y = ?, onrtenet = ?, hasmajor = ? WHERE id = ?");
      selLinkRangePS_ = conn_.prepareStatement("SELECT * FROM links WHERE ? > bbx1 AND ? < bbx2 AND ? < bby2 AND ? > bby1");
      updateAddrRangePS_ = conn_.prepareStatement("UPDATE addrranges SET faddrl = ?, faddrr = ?, taddrl = ?, taddrr = ?, zipl = ?, zipr = ? WHERE id = ?");
      queryShpPtsPS_ = conn_.prepareStatement("SELECT * FROM shapepoints WHERE id = ?");
      updateShpPtsPS_ = conn_.prepareStatement("UPDATE shapepoints SET shppts = ? WHERE id = ?");
      insertElevPS_ = conn_.prepareStatement("INSERT INTO elevation VALUES (?,?,?,?)");
      insertScenLinkTypePS_ = conn_.prepareStatement("INSERT INTO scenlinktypes VALUES (?,?,?)");
      deleteScenLinkTypesPS_ = conn_.prepareStatement("DELETE FROM scenlinktypes WHERE link = ?");
      /*if (dataPkg_.getProperty("logQueries").equals("true")) {
        insertTripLogPS_ = conn_.prepareStatement("INSERT INTO tripqueries VALUES (NULL,?,?,now(),?,?,?,?,?,?,?,?,?,?,?,?)");
        insertLocationLogPS_ = conn_.prepareStatement("INSERT INTO locqueries VALUES (NULL,?,?,now(),?,?,?)");
      }

      // server only statements:
      if(engine_.getProperty(SysProps.APP_MODE).equals("server")) {
        queryTripLinksPS_ = conn_.prepareStatement("SELECT * FROM triplinks WHERE id = ?");
        insertTripLinksPS_ = conn_.prepareStatement("INSERT INTO triplinks VALUES (NULL,now(),?)");
      }*/
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }

  // METHODS LEFT TO RDBMS-SPECIFIC IMPLEMENTATIONS
  public abstract void init() throws SQLException;

  public abstract int getRDBMS();

  public abstract String jdbcClassname();

  public abstract void close();

  public abstract void dumpToZip(ZipOutputStream zos);

  public abstract void readFromZip(ZipFile zipFile);

  public abstract void delete();

  // TABLE CREATION/DELETION
  public void createBaseNetTables() {
    try {
      createNodesTable();
      createLinksTable();
      createShapePointsTable();
      createAddrTable();
      createIsectTable();
      createElevationTable();
      createLandmarksTable();
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public void createNodesTable() {
    try {
      s_.execute("create table nodes(id int not null primary key, scenario int, x double, y double, onrtenet smallint, hasmajor smallint)");
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }

  public void createLinksTable() {
    //try { s_.execute("create table links(id int not null primary key, type int, fnode int, tnode int, name varchar(64), len double, onrtenet smallint, bbx1 int, bby1 int, bbx2 int, bby2 int)"); }
    try {
      s_.execute("create table links(id int not null primary key, scenario int, type int, fnode int, tnode int, name varchar(64), len double, tiles varchar(64), onrtenet smallint, bbx1 int, bby1 int, bbx2 int, bby2 int)");
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public void createAddrTable() {
    try {
      s_.execute("create table addrranges(id int not null primary key, faddrl int, faddrr int, taddrl int, taddrr int, zipl int, zipr int)");
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public void createElevationTable() {
    try {
      s_.execute("create table elevation(id int not null primary key, elevlist blob, upslopefw float, upslopebw float)");
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public void createIsectTable() {
    try {
      s_.execute("create table isectkeys(isectkey varchar(64), node int)");
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public void createScenLinkTypesTable() {
    try {
      s_.execute("create table scenlinktypes(scenario int, link int, type int)");
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public void createLandmarksTable() {
    try {
      s_.execute("create table landmarks(name varchar(64), loc varchar(64), altnames varchar(256))");
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public void createShapePointsTable() throws SQLException {
    s_.execute("create table shapepoints(id int not null primary key, shppts blob)");
  }

  /*public void createQueryLogTables() {
    try {
      s_.execute("create table tripqueries(id int primary key auto_increment, refid int, sessid int, servertime datetime, scenario int, triptype smallint, startloc varchar(128), endloc varchar(128), triptime int, tripday smallint, timemode smallint, nmspeed float, nmradius float, typefact float, topfact float, result smallint)");
      s_.execute("create table locqueries(id int primary key auto_increment, refid int, sessid int, servertime datetime, scenario int, queryloc varchar(128), result smallint)");
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public void createTripLinksTable() {
    try {
      s_.execute("create table triplinks(id int primary key auto_increment, servertime datetime, list text)");
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }*/

  public void createPingTable() {
    try {
      s_.execute("create table ping(id int primary key auto_increment, value varchar(16))");
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
    
  }
  
  // DROP TABLE METHODS
  public void dropTable(String table) {
    try {
      s_.execute("drop table " + table);
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public void dropBaseNetTables() {
    try {
      s_.execute("drop table nodes");
      s_.execute("drop table links");
      s_.execute("drop table shapepoints");
      s_.execute("drop table addrranges");
      s_.execute("drop table isectkeys");
      s_.execute("drop table landmarks");
      s_.execute("drop table elevation");
    } catch (Throwable e) {
      //System.out.println("exception thrown:");
      //e.printStackTrace();
    }
  }

  public void clearBaseNetTables() {
    dropBaseNetTables();
    createBaseNetTables();
  }

  // GENERAL UTILITY
  public int countRows(String table) {
    int rows = 0;
    try {
      ResultSet rs = s_.executeQuery("SELECT * FROM " + table);
      while (rs.next()) {
        rows++;
      }
      rs.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
    return rows;
  }

  public void updateAttribute(String table, String idName, String id, String attr, String val) {
    try {
      String query = "UPDATE " + table + " SET " + attr + " = \"" + val + "\"" +
              " WHERE " + idName + " = \"" + id + "\"";
      s_.executeUpdate(query);
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::updateAttribute():");
      e.printStackTrace();
    }
  }

  // text-based consolidated insertion methods
  public void setActiveTable(String tableName) {
    activeTable_ = tableName;
    insOpsCount_ = 0;
  }

  public void flushInsertOps() {
    String query = "";
    try {
      query = "insert into " + activeTable_ + " values " + insOps_;
      s_.execute(query);
    } catch (Throwable e) {
      System.out.println("q: " + query);
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
    insOps_ = "";
    insOpsCount_ = 0;
  }

  // BLOB-based consolidated insertion actions
  public void setActiveBlobTable(String tableName) {
    activeBlobTable_ = tableName;
    blobInsOpsCount_ = 0;
  }

  public void initSPBlob(int id, byte[] bytes) {
    try {

      if (blobInsOpsCount_ > 0) {
        blobInsOps_ += ",";
      }
      blobInsOps_ += "(" + id + ",?)";
      blobInsOpsCount_++;
      blobs_.add(new ByteArray(bytes));
      if (blobInsOpsCount_ == maxBlobInsOps_) {
        flushBlobInsertOps();
      }

    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public void flushBlobInsertOps() {
    String query = "";
    try {
      query = "insert into " + activeBlobTable_ + " values " + blobInsOps_;
      PreparedStatement ps = conn_.prepareStatement(query);
      for (int i = 0; i < blobInsOpsCount_; i++) {
        ps.setBytes(i + 1, blobs_.get(i).getBytes());
      }
      if (blobInsOpsCount_ > 0) {
        ps.executeUpdate();
      }
    //s_.execute(query);
    } catch (Throwable e) {
      System.out.println("q: " + query);
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
    blobInsOps_ = "";
    blobInsOpsCount_ = 0;
    blobs_ = new ArrayList<ByteArray>();
  }

  // helper class for BLOB operations 
  public class ByteArray {

    private byte[] array_;

    public ByteArray(byte[] array) {
      array_ = array;
    }

    public byte[] getBytes() {
      return array_;
    }
  }

  // TABLE ENTRY INITIALIZATION METHODS
  public void initNode(BNode node) {

    if (insOpsCount_ > 0) {
      insOps_ += ",";
    }
    insOps_ += "(" + node.getID() + "," + node.getScenario().getID() + "," + node.getX() + "," + node.getY() + "," + node.getOnRteNet() + "," + node.getHasMajorCode(engine_.getBaseNet()) + ")";
    insOpsCount_++;
    if (insOpsCount_ == 256) {
      flushInsertOps();
    }

  }

  public void initNode(int id, int scenID, double x, double y, short onRteNet, short hasMajor) {

    if (insOpsCount_ > 0) {
      insOps_ += ",";
    }
    insOps_ += "(" + id + "," + scenID + "," + x + "," + y + "," + onRteNet + "," + hasMajor + ")";
    insOpsCount_++;
    if (insOpsCount_ == 256) {
      flushInsertOps();
    }

  }

  public void initLink(BLink link) {

    String name = link.getDisplayName().replaceAll("'", "");
    Rectangle2D r = link.getBoundingBox();

    if (insOpsCount_ > 0) {
      insOps_ += ",";
    }
    //System.out.println("init link in db, type="+link.getExtendedType());
    insOps_ += "(" + link.getID() + "," + link.getScenario().getID() + "," + link.getExtendedType() + "," + link.getFNodeID() + "," + link.getTNodeID() + ",'" + name + "'," + link.getLengthFeet() + ",'" + link.getTileStr() + "'," + link.getOnRteNet() + "," + ((int) r.getX()) + "," + ((int) r.getY()) + "," + ((int) (r.getX() + r.getWidth())) + "," + ((int) (r.getY() + r.getHeight())) + ")";
    insOpsCount_++;
    if (insOpsCount_ == 256) {
      flushInsertOps();
    }

  }

  public void initLink(int id, int scenID, int type, int fNodeID, int tNodeID, String name, double len, String tiles, short onRteNet, int bbx1, int bby1, int bbx2, int bby2) {

    name = name.replaceAll("'", "");

    if (insOpsCount_ > 0) {
      insOps_ += ",";
    }
    insOps_ += "(" + id + "," + scenID + "," + type + "," + fNodeID + "," + tNodeID + ",'" + name + "'," + len + ",'" + tiles + "'," + onRteNet + "," + bbx1 + "," + bby1 + "," + bbx2 + "," + bby2 + ")";
    insOpsCount_++;
    if (insOpsCount_ == 256) {
      flushInsertOps();
    }

  }

  public void initAddrRange(BLinkStreet link) {
    this.initAddrRange(link.getID(), link.getFAddrL(), link.getFAddrR(), link.getTAddrL(), link.getTAddrR(), link.getZipL(), link.getZipR());
  }

  public void initAddrRange(int id, int fAddrL, int fAddrR, int tAddrL, int tAddrR, int zipL, int zipR) {
    if (insOpsCount_ > 0) {
      insOps_ += ",";
    }
    insOps_ += "(" + id + "," + fAddrL + "," + fAddrR + "," + tAddrL + "," + tAddrR + "," + zipL + "," + zipR + ")";
    insOpsCount_++;
    if (insOpsCount_ == 256) {
      flushInsertOps();
    }

  }

  public void initIsectKey(String key, int nodeID) {

    if (insOpsCount_ > 0) {
      insOps_ += ",";
    }
    insOps_ += "('" + key + "'," + nodeID + ")";
    insOpsCount_++;
    if (insOpsCount_ == 256) {
      flushInsertOps();
    }

  }

  public void initLandmark(String name, String loc) {
    initLandmark(name, loc, "");
  }

  public void initLandmark(String name, String loc, String altnames) {

    name = name.replaceAll("'", "");
    loc = loc.replaceAll("'", "");
    altnames = altnames.replaceAll("'", "");
    if (insOpsCount_ > 0) {
      insOps_ += ",";
    }
    insOps_ += "('" + name + "','" + loc + "', '" + altnames + "')";
    insOpsCount_++;
    if (insOpsCount_ == 256) {
      flushInsertOps();
    }

  }

  public void initElevInfo(BLink link) {
    try {
      insertElevPS_.setInt(1, link.getID());
      insertElevPS_.setBytes(2, link.getElevBytes());
      insertElevPS_.setFloat(3, link.getUpSlopeFW());
      insertElevPS_.setFloat(4, link.getUpSlopeBW());
      insertElevPS_.executeUpdate();
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public void initElevInfo(int id, byte[] bytes, float upslopefw, float upslopebw) {
    try {
      insertElevPS_.setInt(1, id);
      insertElevPS_.setBytes(2, bytes);
      insertElevPS_.setFloat(3, upslopefw);
      insertElevPS_.setFloat(4, upslopebw);
      insertElevPS_.executeUpdate();
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  // BASIC DB RETRIEVAL METHODS 
  public int getMaxID(String table) {
    try {
      Statement s = conn_.createStatement();
      ResultSet rs = s.executeQuery("SELECT max(id) FROM " + table);
      if (rs.next()) {
        int max = rs.getInt(1);
        rs.close();
        s.close();
        return max;
      }
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
    return 0;
  }

  public double getMinMax(String minmax, String var, String table) {
    try {
      Statement s = conn_.createStatement();
      ResultSet rs = s.executeQuery("SELECT " + minmax + "(" + var + ") FROM " + table);
      if (rs.next()) {
        double max = rs.getDouble(1);
        rs.close();
        s.close();
        return max;
      }
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
    return 0;
  }

  public synchronized String getLinkName(int id) {
    String ret = "";
    try {
      //System.out.println("gln for "+id);
      ResultSet rs = s_.executeQuery("SELECT name FROM links WHERE id = " + id);
      if (rs.next()) {
        ret = rs.getString(1);
      }
      rs.close();

    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
      System.exit(1);
    }
    //System.out.println("returning: "+ret);		
    return ret;
  }

  public List<BNode> getNodes(double x1, double y1, double x2, double y2, int majorCode) {
    List ret = new LinkedList<BNode>();
    try {
      //ResultSet rs = s_.executeQuery("SELECT id FROM nodes WHERE x > "+x1+" AND x < "+x2+" AND y > "+y1+" AND y < "+y2);
      ResultSet rs = s_.executeQuery("SELECT * FROM nodes WHERE x > " + x1 + " AND x < " + x2 + " AND y > " + y1 + " AND y < " + y2 + (majorCode > 0 ? " AND hasmajor >= " + majorCode : ""));
      while (rs.next()) {
        int id = rs.getInt(1);
        //System.out.println("n id: "+id);
        if (engine_.getNodeFromID(id) == null) {
          engine_.loadSingleNode(rs);
        }
        ret.add(engine_.getNodeFromID(new Integer(id)));
      }
      rs.close();

    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
    //System.out.println("returning "+ret.size()+" nodes");
    return ret;//.iterator();
  }

  public List<BNode> getNearbyNodes(double x, double y, double d) {
    List ret = new LinkedList<BNode>();
    try {
      ResultSet rs = s_.executeQuery("SELECT * FROM nodes WHERE x > " + (x - d) + " AND x < " + (x + d) + " AND y > " + (y - d) + " AND y < " + (y + d));
      while (rs.next()) {
        int id = rs.getInt(1);
        //System.out.println("n id: "+id);
        if (engine_.getNodeFromID(id) == null) {
          engine_.loadSingleNode(rs);
        }
        ret.add(engine_.getNodeFromID(new Integer(id)));
      }
      rs.close();

    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
    //System.out.println("returning "+ret.size()+" nodes");
    return ret;//.iterator();
  }

  public Set<BLink> getLinks(int x1, int y1, int x2, int y2) {
    //System.out.println(x1+", "+y1+" to "+x2+", "+y2);
    Set ret = new HashSet<BLink>();
    try {
      //ResultSet rs = s_.executeQuery("SELECT * FROM links WHERE "+x2+" > bbx1 AND "+x1+" < bbx2 AND "+y1+" < bby2 AND "+y2+" > bby1");
      selLinkRangePS_.setInt(1, x2);
      selLinkRangePS_.setInt(2, x1);
      selLinkRangePS_.setInt(3, y1);
      selLinkRangePS_.setInt(4, y2);
      ResultSet rs = selLinkRangePS_.executeQuery();
      while (rs.next()) {
        int id = rs.getInt(1);
        //System.out.println("n id: "+id);
        if (engine_.getLinkFromID(id) == null) {
          engine_.loadSingleLink(rs);
        }
        ret.add(engine_.getLinkFromID(new Integer(id)));
      }
      rs.close();

    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
    //System.out.println("returning "+ret.size()+" nodes");
    return ret;//.iterator();
  }

  public List<BLink> getMajorLinks() {
    List ret = new LinkedList<BLink>();
    try {
      ResultSet rs = s_.executeQuery("SELECT * FROM links WHERE type/100 < 20");
      while (rs.next()) {
        int id = rs.getInt(1);
        //System.out.println("n id: "+id);
        if (engine_.getLinkFromID(id) == null) {
          engine_.loadSingleLink(rs);
        }
        ret.add(engine_.getLinkFromID(new Integer(id)));
      }
      rs.close();

    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
    return ret;
  }

  public List<BLink> getTransitLinks() {
    List ret = new LinkedList<BLink>();
    try {
      ResultSet rs = s_.executeQuery("SELECT * FROM links WHERE type/100 >= 60");
      while (rs.next()) {
        int id = rs.getInt(1);
        //System.out.println("n id: "+id);
        if (engine_.getLinkFromID(id) == null) {
          engine_.loadSingleLink(rs);
        }
        ret.add(engine_.getLinkFromID(new Integer(id)));
      }
      rs.close();

    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
    return ret;
  }

  public ResultSet getFromID(String table, int id) {
    try {
      return s_.executeQuery("SELECT * FROM " + table + "WHERE id="+id);
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::getAllRows");
      e.printStackTrace();
    }
    return null;
  }
    
  public ResultSet getAllRows(String table) {
    try {
      return s_.executeQuery("SELECT * FROM " + table);
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::getAllRows");
      e.printStackTrace();
    }
    return null;
  }

  public ResultSet getAllNodes() {
    return getAllRows("nodes");
  }

  public ResultSet getRteNetNodes() {
    try {
      return s_.executeQuery("SELECT * FROM nodes WHERE onrtenet=1");
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::getAllRows");
      e.printStackTrace();
    }
    return null;
  }

  public ResultSet getAllLinks() {
    return getAllRows("links");
  }

  public ResultSet getRteNetLinks() {
    try {
      return s_.executeQuery("SELECT * FROM links WHERE onrtenet=1");
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::getAllRows");
      e.printStackTrace();
    }
    return null;
  }

  public ResultSet getFromSet(String table, Set<Integer> idSet) {
    String query = "SELECT * FROM " + table + " WHERE id IN (";
    Iterator<Integer> ids = idSet.iterator();
    while (ids.hasNext()) {
      query += ids.next();
      if (ids.hasNext()) {
        query += ",";
      }
    }
    query += ")";
    //System.out.println("query: "+query);
    try {
      return s_.executeQuery(query);
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::getFromSet");
      e.printStackTrace();
    }
    return null;
  }

   
  // ADVANCED QUERY METHODS
  public ResultSet queryLandmarks(String nameOnly) {
    nameOnly = nameOnly.trim().toLowerCase();
    String query = "SELECT * FROM landmarks WHERE lcase(name) LIKE '%" + nameOnly + "%' OR lcase(altnames) LIKE '%" + nameOnly + "%'";
    //System.out.println(query);
    try {
      return s_.executeQuery(query);
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::queryLandmarks");
      e.printStackTrace();
    }
    return null;
  }

  public ResultSet queryStreetNodes(String nameOnly) {
    String query = "SELECT nodes.id FROM nodes INNER JOIN links ON (nodes.id = links.fnode OR nodes.id = links.tnode) AND lcase(links.name) LIKE '%" + nameOnly + "%'";
    try {
      return s_.executeQuery(query);
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::queryStreetLinks");
      e.printStackTrace();
    }
    return null;
  }

  public ResultSet queryStreetLinks(String nameOnly) {
    String query = "SELECT * FROM links WHERE type/100 < 50 AND lcase(name) LIKE '%" + nameOnly + "%'";
    try {
      return s_.executeQuery(query);
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::queryStreetLinks");
      e.printStackTrace();
    }
    return null;
  }

  public ResultSet queryIsectKeys(String isectKey) {
    String query = "SELECT * FROM isectkeys WHERE isectkey = '" + isectKey + "'";
    try {
      return s_.executeQuery(query);
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::queryIsectKeys");
      e.printStackTrace();
    }
    return null;
  }

  // MODIFY/DELETE EXISTING ENTRIES
  public void deleteNode(BNode node) {
    try {
      s_.executeUpdate("DELETE FROM nodes WHERE id = " + node.getID());
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::deleteNode:");
      e.printStackTrace();
    }
  }
  
  public void deleteNodes(Iterator<BNode> nodes) {
    String ids = "(";
    while(nodes.hasNext()) {
      ids += nodes.next().getID();
      if(nodes.hasNext()) ids += ","; 
    }
    ids += ")";
    try {
      s_.executeUpdate("DELETE FROM nodes WHERE id IN "+ids);
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::deleteNodes(nodes):");
      e.printStackTrace();
    }
  }
  
  public void deleteLink(BLink link) {
    deleteLink(link.getID());
  }

  public void deleteLink(int id) {
    try {
      s_.executeUpdate("DELETE FROM links WHERE id = " + id);
      s_.executeUpdate("DELETE FROM shapepoints WHERE id = " + id);
      s_.executeUpdate("DELETE FROM addrranges WHERE id = " + id);
      s_.executeUpdate("DELETE FROM elevation WHERE id = " + id);
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::deleteLink(id):");
      e.printStackTrace();
    }
  }

  public void deleteLinks(Iterator<BLink> links) {
    String ids = "(";
    while(links.hasNext()) {
      ids += links.next().getID();
      if(links.hasNext()) ids += ","; 
    }
    ids += ")";
    try {
      s_.executeUpdate("DELETE FROM links WHERE id IN "+ids);
      s_.executeUpdate("DELETE FROM shapepoints WHERE id IN "+ids);
      s_.executeUpdate("DELETE FROM addrranges WHERE id IN "+ids);
      s_.executeUpdate("DELETE FROM elevation WHERE id IN "+ids);
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::deleteLinks(links):");
      e.printStackTrace();
    }
  }
    
  public void updateNode(BNode node) {
    try {
      updateNodePS_.setInt(1, node.getScenario().getID());
      updateNodePS_.setDouble(2, node.getX());
      updateNodePS_.setDouble(3, node.getY());
      updateNodePS_.setShort(4, node.getOnRteNet());
      updateNodePS_.setShort(5, node.getHasMajorCode(engine_.getBaseNet()));
      updateNodePS_.setInt(6, node.getID());
      updateNodePS_.executeUpdate();
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::updateLink:");
      e.printStackTrace();
    }
  }

  public void updateNodes(Iterator<BNode> nodes) {
    try {
      updateNodePS_.clearBatch();
      while (nodes.hasNext()) {
        BNode node = nodes.next();
        updateNodePS_.setInt(1, node.getScenario().getID());
        updateNodePS_.setDouble(2, node.getX());
        updateNodePS_.setDouble(3, node.getY());
        updateNodePS_.setShort(4, node.getOnRteNet());
        updateNodePS_.setShort(5, node.getHasMajorCode(engine_.getBaseNet()));
        updateNodePS_.setInt(6, node.getID());
        updateNodePS_.addBatch();
      }
      updateNodePS_.executeBatch();
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::updateNodes:");
      e.printStackTrace();
    }
  }

  public void updateLink(BLink link) {
    try {
      updateLinkPS_.setInt(1, link.getExtendedType());
      updateLinkPS_.setInt(2, link.getScenario().getID());
      updateLinkPS_.setInt(3, link.getFNodeID());
      updateLinkPS_.setInt(4, link.getTNodeID());
      updateLinkPS_.setString(5, link.getDisplayName());
      updateLinkPS_.setInt(6, link.getLengthFeet());
      updateLinkPS_.setShort(7, link.getOnRteNet());
      Rectangle2D bb = link.getBoundingBox();
      updateLinkPS_.setInt(8, (int) bb.getX());
      updateLinkPS_.setInt(9, (int) bb.getY());
      updateLinkPS_.setInt(10, (int) (bb.getX() + bb.getWidth()));
      updateLinkPS_.setInt(11, (int) (bb.getY() + bb.getHeight()));
      updateLinkPS_.setInt(12, link.getID());

      updateLinkPS_.executeUpdate();
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::updateLink:");
      e.printStackTrace();
    }
  }

  public void updateShapePoints(BLink link) {
    try {
      //String query = "SELECT id FROM shapepoints WHERE id = "+link.getID();
      //System.out.println(query);
      //ResultSet rs = s_.executeQuery(query);
      queryShpPtsPS_.setInt(1, link.getID());
      ResultSet rs = queryShpPtsPS_.executeQuery();
      if (rs.next()) { // already exists
        byte[] bytes = link.getShapePointsBytes();
        System.out.println("bytelen=" + bytes.length);
        updateShpPtsPS_.setBytes(1, bytes);
        updateShpPtsPS_.setInt(2, link.getID());
        updateShpPtsPS_.executeUpdate();
      } else {
        activeBlobTable_ = "shapepoints";
        initSPBlob(link.getID(), link.getShapePointsBytes());
        flushBlobInsertOps();
      }
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::updateLink:");
      e.printStackTrace();
    }
  }

  public void updateLandmark(String name, String loc, String altnames) {
    try {
      String query = "UPDATE landmarks SET " +
              "loc = '" + loc + "'," +
              "altnames = '" + altnames + "'" +
              " WHERE name = '" + name + "'";

      System.out.println("q: " + query);
      s_.executeUpdate(query);
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::updateLink:");
      e.printStackTrace();
    }
  }

  public void updateLinks(Iterator<BLink> links) {
    try {
      updateLinkPS_.clearBatch();
      while (links.hasNext()) {
        BLink link = links.next();
        updateLinkPS_.setInt(1, link.getExtendedType());
        updateLinkPS_.setInt(2, link.getScenario().getID());
        updateLinkPS_.setInt(3, link.getFNodeID());
        updateLinkPS_.setInt(4, link.getTNodeID());
        updateLinkPS_.setString(5, link.getDisplayName());
        updateLinkPS_.setInt(6, link.getLengthFeet());
        updateLinkPS_.setShort(7, link.getOnRteNet());
        Rectangle2D bb = link.getBoundingBox();
        updateLinkPS_.setInt(8, (int) bb.getX());
        updateLinkPS_.setInt(9, (int) bb.getY());
        updateLinkPS_.setInt(10, (int) (bb.getX() + bb.getWidth()));
        updateLinkPS_.setInt(11, (int) (bb.getY() + bb.getHeight()));
        updateLinkPS_.setInt(12, link.getID());
        updateLinkPS_.addBatch();
      }
      updateLinkPS_.executeBatch();
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::updateLink:");
      e.printStackTrace();
    }
  }

  public void updateAddrRange(BLinkStreet link) {
    updateAddrRange(link, link.getFAddrL(), link.getFAddrR(), link.getTAddrL(), link.getTAddrR(), link.getZipL(), link.getZipR());
  }

  public void updateAddrRange(BLinkStreet link, int fAddrL, int fAddrR, int tAddrL, int tAddrR, int zipL, int zipR) {
    try {
      updateAddrRangePS_.setInt(1, fAddrL);
      updateAddrRangePS_.setInt(2, fAddrR);
      updateAddrRangePS_.setInt(3, tAddrL);
      updateAddrRangePS_.setInt(4, tAddrR);
      updateAddrRangePS_.setInt(5, zipL);
      updateAddrRangePS_.setInt(6, zipR);
      updateAddrRangePS_.setInt(7, link.getID());
      updateAddrRangePS_.executeUpdate();
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::updateAddrRange():");
      e.printStackTrace();
    }
  }

  public void deleteScenLinkTypes(Iterator<BLinkStreet> links) {
    try {
      deleteScenLinkTypesPS_.clearBatch();
      while(links.hasNext()) {
        deleteScenLinkTypesPS_.setInt(1, links.next().getID());
        deleteScenLinkTypesPS_.addBatch();
      }
      deleteScenLinkTypesPS_.executeBatch();
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::deleteScenLinkTypes(id):");
      e.printStackTrace();
    }
  }


  public void initScenLinkTypes(Iterator<BLinkStreet> links) {
    try {
      insertScenLinkTypePS_.clearBatch();
      while(links.hasNext()) {
        BLink link = links.next();
        Map<Short, Integer> overrides = engine_.getBaseNet().getScenLinkOverrides(link.getID());

        for(Iterator<Map.Entry<Short, Integer>> it = overrides.entrySet().iterator(); it.hasNext();) {
          Map.Entry<Short, Integer> entry = it.next();
          insertScenLinkTypePS_.setShort(1, entry.getKey());
          insertScenLinkTypePS_.setInt(2, link.getID());
          insertScenLinkTypePS_.setInt(3, entry.getValue());
          insertScenLinkTypePS_.addBatch();
        }
      }
      insertScenLinkTypePS_.executeBatch();
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::initScenLinkTypes(id):");
      e.printStackTrace();
    }

  }

  public void initScenLinkType(short scenID, int linkID, int newType) {
    try {
      insertScenLinkTypePS_.clearBatch();
      insertScenLinkTypePS_.setShort(1, scenID);
      insertScenLinkTypePS_.setInt(2, linkID);
      insertScenLinkTypePS_.setInt(3, newType);
      insertScenLinkTypePS_.addBatch();
      insertScenLinkTypePS_.executeBatch();
    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::initScenLinkType(.):");
      e.printStackTrace();
    }

  }

  public void initOnRteNet(String table, Iterator<Integer> ids) {
    System.out.println("init onrtenet, table: " + table);
    try {
      //setAutoCommit(false);
      Statement s = conn_.createStatement();
      String query = "ALTER TABLE " + table + " ADD onrtenet smallint";
      s.execute(query);

      int i = 0;
      query = "UPDATE " + table + " SET onrtenet=1 WHERE id IN (";
      while (ids.hasNext()) {
        i++;
        int id = ids.next();
        query += id;
        if (table.equals("nodes") && id == 1000010) {
          System.out.println("FOUND 1000010");
        }
        if (ids.hasNext() && i % 512 != 0) {
          query += ",";
        }
        if (i > 0 && i % 512 == 0) {
          query += ")";
          //System.out.println(query);
          s.executeUpdate(query);
          query = "UPDATE " + table + " SET onrtenet=1 WHERE id IN (";
          System.out.println(i + " ids processed");
        }
      }
      query += ")";
      s.executeUpdate(query);
      System.out.println(i + " total ids processed");
      s.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }

  }

  public void initLinkBBs(Iterator<BLink> links) {
    System.out.println("init linkBBs");
    try {
      //setAutoCommit(false);
      Statement s = conn_.createStatement();
      s.execute("ALTER TABLE links ADD bbx1 int");
      s.execute("ALTER TABLE links ADD bby1 int");
      s.execute("ALTER TABLE links ADD bbx2 int");
      s.execute("ALTER TABLE links ADD bby2 int");
      s.close();
      int i = 0;
      PreparedStatement ps = conn_.prepareStatement("UPDATE links SET bbx1=?, bby1=?, bbx2=?, bby2=? WHERE id=?");
      while (links.hasNext()) {
        i++;
        if (i > 0 && i % 1000 == 0) {
          System.out.println(i + " links processed");
        //System.out.print("executing batch...");
        //ps.executeBatch();
        //ps.clearBatch();
        //System.out.println("done");

        }
        BLink link = links.next();
        ps.setInt(1, (int) link.getBoundingBox().getX());
        ps.setInt(2, (int) link.getBoundingBox().getY());
        ps.setInt(3, (int) (link.getBoundingBox().getX() + link.getBoundingBox().getWidth()));
        ps.setInt(4, (int) (link.getBoundingBox().getY() + link.getBoundingBox().getHeight()));
        ps.setInt(5, link.getID());
        ps.executeUpdate();//  .addBatch();

      }
      System.out.println(i + " links processed total");
    //System.out.print("executing batch...");
    //ps.executeBatch();
    //conn_.commit();
    //System.out.println("done");
    //setAutoCommit(true);

    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }



  // MISC
  public void setAutoCommit(boolean val) {
    try {
      conn_.setAutoCommit(val);
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }

  public void ping() {
    try {
      Statement s = conn_.createStatement();
      String query = "INSERT INTO ping VALUES (1, 'foo')";
      s.executeUpdate(query);

      query = "DELETE FROM ping WHERE id = 1";
      s.executeUpdate(query);

      s.close();

    } catch (Throwable e) {
      System.out.println("exception thrown in LegacyDB::ping()");
      e.printStackTrace();
    }
  }

  public void setPrimaryKey(String table) {
    try {
      Statement s = conn_.createStatement();
      s.executeUpdate("ALTER TABLE " + table + " ALTER COLUMN id not null");
      s.executeUpdate("ALTER TABLE " + table + " ADD PRIMARY KEY (id)");
      s.close();
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }
}
